<?php

CRequire('CObject', 'CError');

require_once 'CMySQLConstants.php';

$GLOBALS['_CMySQLDefault'] = null;

define ('kCMySQLError', 'kCMySQLError');

/*
	How to make conditions:
	
	array(
		'name' => 'value', // equality implied
		'name' => array(kCLessThan, 123), // operator
	);
	
	To correctly quote the parameters, pass data for the
	following SQL types using the following PHP types:
	
	* Integer SQL types (TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT):
		- use an integer number (eg. 'age' => 10).
		All of these are output as integers.
	* Decimal SQL types (NUMERIC, DECIMAL):
		- use a PHP floating-point number value (eg. 'pi' => 3.14).
	* BOOLEAN:
		- use a PHP boolean value.
	* Types that take an input string (eg. CHAR, ENUM, VARCHAR, TEXT, BINARY...)
		- use a PHP string (eg. 'name' => 'John').
		In the future, String Kit strings will be supported for storing in
		character-oriented types (eg CHAR but not BINARY).
		All of these are currently output as PHP strings. This may change in
		the future.
	* DATETIME, DATE and TIMESTAMP:
		- use a CTime object or a correctly-formatted PHP string.
		For example: 'lastmodified' => CTime::Now(), or
		'birthday' => '1985-09-02 16:55:00'.
		All of these are output as strings. To retrieve a CTime object, use
		CTime::FromString(). In the future, a schema may be used to do such
		a conversion automatically.
	* TIME:
		- use a CDuration object or a correctly-formatted PHP string.
		For example: 'runningTime' => CDuration::Minutes(120) or
		'lastTimeSeen' => '12:56:00'.
		TIME is output as a string. To retrieve a CDuration object,
		use CDuration::FromString(). In the future, a schema may be used
		to do such a conversion automatically.
	* YEAR:
		- use a PHP string or integer (eg. 'birthyear' => 1985 or
		'birthyear' => '1985').
		YEAR will be output as a string. You can safely cast the string
		into an integer.
	* SET:
		- use a PHP string of the form 'a,b,c,d,e'
		(eg. 'permissions' => 'read,write,delete').
		SET is output as a string of the form 'a,b,c,d,e'.
		In the future, an array of the form array('a', 'b', 'c') may be
		allowed instead.
		
	Conditions for selection can have the following flags in the condition:
	kCLimit; kCBeginAt; kCSortBy.
	
	See their docuentation for more information.
*/

class CMySQL extends CObject {
	var $_Conn;

	function SetDefaultDB(&$db) {
		$GLOBALS['_CMySQLDefault'] = &$db;
	}
	
	function &DefaultDB() {
		return $GLOBALS['_CMySQLDefault'];
	}
	
	function &Connect($host, $user, $pass, $db, $classes = null) {
		$error = null;
		$db = &new CMySQL($host, $user, $pass, $db, $error, $classes);
		
		if (CErr($error))
			return $error;
		else {
			CMySQL::SetDefaultDB($db);
			return $db;
		}
	}
	
	function Init($host, $user, $pass, $db, &$error) {
		$this->_Conn = @mysql_connect($host, $user, $pass);
		
		if (!$this->_Conn) {
			$error = CMySQL::MySQLError(null);
			return;
		}
		
		if (!(@mysql_select_db($db, $this->_Conn))) {
			$error = CMySQL::MySQLError(null);
			return;
		}
	}
	
	function _OrWhere($conditionSets) {
		$conds = '';
		$first = true;
		
		if (defined('_kCMySQLEnableLogging')) {
			CLog("Making a OR condition from:");
			CDump($conditionSets);
		}	
		
		foreach ($conditionSets as $k => $conditionSet) {
			if (!is_numeric($k) || !is_array($conditionSet)) continue;
			if (!$first) $conds .= ' OR ';
			$conds .= '(' . $this->_Where($conditionSet) . ')';
			$first = false;
		}
		
		return $conds;
	}
	
	function _Where($conditions) {	
		$conds = '';
		$first = true;
		
		if (defined('_kCMySQLEnableLogging')) {
			CLog("Making a condition from:");
			CDump($conditions);
		}
		
		$isNot = in_array(kCNot, $conditions);
		
		if ($isNot)
			$conds .= 'NOT (';
		
		foreach ($conditions as $col => $expr) {
			if ($col[0] == '*') {
				if (defined('_kCMySQLEnableLogging')) CLog("Skipping $col");
				continue;
			}
			
			if (is_int($col)) {
				if ($expr != kCNot) {
					CLog("Making OR...");
					if ($isNot)
						return "NOT (" . $this->_OrWhere($conditions) . ")";
					else
						return $this->_OrWhere($conditions);
				} else
					continue;
			}

			if (!$first) $conds .= ' AND';
			
			if (!is_array($expr))
				$expr = array(kCEquals, $expr);
			
			switch ($expr[0]) {
				case kCEquals:
					$conds .= " $col = " . $this->_Quote($expr[1]);
					break;
				case kCGreaterThan:
					$conds .= " $col > " . $this->_Quote($expr[1]);
					break;
				case kCLessThan:
					$conds .= " $col < " . $this->_Quote($expr[1]);
					break;
				case kCGreaterOrEqualTo:
					$conds .= " $col >= " . $this->_Quote($expr[1]);
					break;
				case kCLessOrEqualTo:
					$conds .= " $col >= " . $this->_Quote($expr[1]);
					break;
				case kCNotEquals:
					$conds .= " $col <> " . $this->_Quote($expr[1]);
					break;
				case kCLike:
					$conds .= " $col LIKE " . $this->_Quote($expr[1]);
					break;
				case kCBetween:
					$conds .= " $col BETWEEN " . $this->_Quote($expr[1]) . " AND " . $this->_Quote($expr[2]);
					break;
			}
			
			$first = false;
		}
		
		if ($isNot)
			$conds .= ')';
		
		if ($conds == '')
			return null;
		else
			return $conds;
	}
	
	function _Quote(&$val) {
		if (defined('_kCMySQLEnableLogging')) {
			CLog("Quoting value:");
			CDump($val);
		}	
		
		if (is_int($val) || is_float($val))
			return $val;
		else if (is_bool($val))
			return $val? "true" : "false";
		else if (is_string($val))
			return "'" . mysql_real_escape_string($val, $this->_Conn) . "'";
		else if (is_a($val, 'CTime')) {
			// 'YYYY-MM-DD HH:MM:SS'
			$str = $val->Format('Y-m-d H:i:s');
			return $this->_Quote($str);
		} else if (is_a($val, 'CDuration')) {
			// '[-]HH:MM:SS'
			$secondsVal = $val->InSeconds();
			if ($secondsVal > 0)
				$sign = '';
			else {
				$sign = '-';
				$secondsVal = -$secondsVal;
			}
			$hours = floor($secondsVal / 3600);
			$minutes = abs(floor($secondsVal / 60) - $hours * 60);
			$seconds = abs($secondsVal - $minutes * 60 - $hours * 3600);
			$str = sprintf("%s%02d:%02d:%02d", $sign, $hours, $minutes, $seconds);
			return $this->_Quote($str);
		}
	}
	
	function Find($table, $conditions = null) {
		if (@$this == null || !is_a($this, 'CMySQL'))
			$self = &CMySQL::DefaultDB();
		else
			$self = &$this;
		
		$query = "SELECT * FROM $table";
		
		if (!is_null($conditions)) {
			$where = $self->_Where($conditions);
			$query .= " WHERE $where";
		}
			
		if (isset($conditions[kCSortBy])) {
			$query .= " ORDER BY ";
			if (is_string($conditions[kCSortBy])) {
				$query .= $conditions[kCSortBy] . " ";
				if (@$conditions[kCSortAscending])
					$query .= ' ASC';
				else if (@$conditions[kCSortAscending] === false)
					$query .= ' DESC';
			} else {
				foreach ($conditions[kCSortBy] as $sb) {
					$query .= " $sb ";
					if (@$conditions[kCSortAscending])
						$query .= ' ASC';
					else if (@$conditions[kCSortAscending] === false)
						$query .= ' DESC';
				}
			}
		}
		
		if (isset($conditions[kCLimit]) &&
			is_finite($conditions[kCLimit])) {
			$query .= " LIMIT";
			if (isset($conditions[kCBeginAt]))
				$query .= " {$conditions[kCBeginAt]},";
			$query .= $conditions[kCLimit];
		}
		
		if (defined('_kCMySQLEnableLogging')) {
			CLog("About to execute query:");
			CDump($query);
		}	
		
		$result = @mysql_query($query, $self->_Conn);

		if (!$result)
			return CMySQL::MySQLError(null);
		$results = array();
		
		while (is_array($r = mysql_fetch_assoc($result)))
			$results[] = $r;
		
		return $results;
	}

	function Update($entity, $conditions, $record) {
		if (@$this == null || !is_a($this, 'CMySQL'))
			$self = &CMySQL::DefaultDB();
		else
			$self = &$this;
		
		$query = "UPDATE $entity SET ";
		$notFirst = false;
		foreach ($record as $name => $value) {
			if ($notFirst) $query .= ', ';
			$query .= $name . ' = ' . $self->_Quote($value);
			$notFirst = true;
		}
		
		$query .= " WHERE " . $self->_Where($conditions);

		if (defined('_kCMySQLEnableLogging')) {
			CLog("About to execute query:");
			CDump($query);
		}
				
		return mysql_query($query, $self->_Conn)? true : CMySQL::MySQLError(null);
	}

	function Save($entity, $record) {
		if (@$this == null || !is_a($this, 'CMySQL'))
			$self = &CMySQL::DefaultDB();
		else
			$self = &$this;
		
		$cols = "INSERT INTO $entity (";
		$vals = '(';
		$updates = '';
		$notFirst = false;
		
		foreach ($record as $name => $value) {
			
			if (is_null($value))
				continue;
			
			if ($notFirst) {
				$cols .= ', ';
				$vals .= ', ';
				$updates .= ', ';
			}
			
			$cols .= $name;
			$vals .= $self->_Quote($value);
			$updates .= "$name = VALUES($name)";
			
			$notFirst = true;
		}
		
		$cols .= ')';
		$vals .= ')';
		
		$query = "$cols VALUES $vals ON DUPLICATE KEY UPDATE $updates";
		
		if (defined('_kCMySQLEnableLogging')) {
			CLog("About to execute query:");
			CDump($query);
		}
		
		$ret = mysql_query($query, $self->_Conn)? true : CMySQL::MySQLError(null);
		if (CErr($ret)) return $ret;
		
		$i = mysql_insert_id();
		return (!$i)? true : $i;
	}
	
	function Delete($table, $conds) {
		if (@$this == null || !is_a($this, 'CMySQL'))
			$self = &CMySQL::DefaultDB();
		else
			$self = &$this;
		
		$query = sprintf("DELETE FROM %s WHERE %s", $table, $self->_Where($conds));
		
		if (defined('_kCMySQLEnableLogging')) {
			CLog("About to execute query:");
			CDump($query);
		}
		
		return mysql_query($query, $self->_Conn)? true : CMySQL::MySQLError(null);
	}
	
	function MySQLError($reason) {
		return new CError(kCMySQLError, "A database error occurred when executing the last command or query: " . mysql_error(), $reason);
	}
}

?>